
** Medical expenditures, insured, uninsured, and retirees. 

qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
keep if AGE1<65 & AGE1>24
keep if INSCOP_Y1==1 // this covers R1-R3 which we are using here.

**** CHARGES 
gen I_Charges = 1 if Charges_plus_RX<.5 & Charges_plus_RX!=.
replace I_Charges = 0 if Charges_plus_RX>=.5 & Charges_plus_RX!=.

gen I_EXP = 1 if Expenditures_incl_RX<.5 & Expenditures_incl_RX!=.
replace I_EXP = 0 if Expenditures_incl_RX>=.5 & Expenditures_incl_RX!=.

gen I_suspicious1 = 1 if Charges_plus_RX<Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.
replace I_suspicious1 = 0 if Charges_plus_RX>=Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.
tab  I_suspicious1
}

**** STATS FOR TABLE 35 IN APPENDIX
** TABLE: fractions with 0 charges and medical exp. - copy paste into appendix table. 
qui{

gen PHI=0 if INSCOV_Y1==2 | INSCOV_Y1==3
replace PHI=1 if INSCOV_Y1==1

* ALL
table PHI if I_suspicious1 == 0 [aw=Sample_Weight] , stat(mean I_Charges)
table INSCOV_Y1 if I_suspicious1 == 0 [aw=Sample_Weight] , stat(mean I_Charges )
* by health shocks - top panel of Table 35 in appendix
table type2 PHI if I_suspicious1 == 0 [aw=Sample_Weight] , stat(mean I_Charges ) //NON-PHI
table type2  if INSCOV_Y1==3 & I_suspicious1 == 0 [aw=Sample_Weight] , stat(mean I_Charges ) // Uninsured
table type2  if INSCOV_Y1==1 & I_suspicious1 == 0 [aw=Sample_Weight] , stat(mean I_Charges ) // has private
* by Health -- bottom panel of Table 35 in appendix
table H1 if INSCOV_Y1==3 & I_suspicious1 == 0 [aw=Sample_Weight] , stat(mean  I_Charges)
table H1 if INSCOV_Y1==1 & I_suspicious1 == 0 [aw=Sample_Weight] , stat(mean  I_Charges)
table H1 if PHI==0 & I_suspicious1 == 0 [aw=Sample_Weight] , stat(mean  I_Charges)
}

* ESTIMATE MEDICAL COSTS


* FOR INSURED, WE WANT OOP
* exclude Medicaid,  exclude if charges smaller than med. 
* we want the bottom 95 and top 5% (non-catastrophic and catastrophic)
qui{
gen MCAID_yr1= 0 if MCAID1==0 & MCAID2==0 & MCAID3==0 
replace MCAID_yr1= 1 if MCAID1==1 | MCAID2==1 | MCAID3==1
tabstat MCAID_yr1 [aw=Sample_Weight] if PRIVAT1==1 &  H1!=. & TOTSLF_Y1!=. & type2!=.  
tabstat I_suspicious1 [aw=Sample_Weight] if PRIVAT1==1 &  H1!=. & TOTSLF_Y1!=. & type2!=.  


gen age_group1=25 if AGE1>=25 & AGE1<30
replace age_group1=30 if AGE1>=30 & AGE1<35
replace age_group1=35 if AGE1>=35 & AGE1<40
replace age_group1=40 if AGE1>=40 & AGE1<45
replace age_group1=45 if AGE1>=45 & AGE1<50
replace age_group1=50 if AGE1>=50 & AGE1<55
replace age_group1=55 if AGE1>=55 & AGE1<60
replace age_group1=60 if AGE1>=60 & AGE1<65


label var TOTSLF_Y1 "OOP"
label var Charges_plus_RX "Medical Charges"

**** exp/charges by different groups
gen ratio_Exp_Charg = Expenditures_incl_RX/Charges_plus_RX
gen ratio_OOP_Exp = TOTSLF_Y1/Expenditures_incl_RX

**** KEEP THOSE WITH INSURANCE
keep if INSCOV_Y1==1  & MCAID_yr1== 0 & I_suspicious1 == 0 & H1!=. & TOTSLF_Y1!=. & type2!=.  


bysort age_group1 H1 type2: egen p95=pctile(TOTSLF_Y1) , p(95)
gen OOP1=TOTSLF_Y1 if TOTSLF_Y1<p95 
gen OOP2=TOTSLF_Y1 if TOTSLF_Y1>=p95  & TOTSLF_Y1!=.


fillin age_group1 H1 type2
reg OOP1 i.age_group1 i.H1 i.type2 
predict SLF1 if OOP1!=. | _fillin==1
reg OOP2 i.age_group1 i.H1 i.type2 
predict SLF2 if OOP2!=.  | _fillin==1

drop if age_group1==.


collapse (mean) SLF1 SLF2, by(age_group1 H1 type2)
gen ratio = SLF1/SLF2
* we have some cells with missing values. impute them based on the ratio.
tabstat ratio
replace SLF1=.1718507*SLF2 if SLF1==. | SLF1<0
drop ratio

label var SLF1 "Non-catastrophic"
label var SLF2 "Catastrophic"
label var age_group1 "Age Group"
label var type2 "Health Shocks"
label define groups 25 "25-29" 30 "30-34" 35 "35-39" 40 "40-44" 45 "45-49" 50 "50-54" 55 "55-59" 60 "60-64"
label values age_group1 groups

* STATS FOR TABLES 26, 27, AND 28 IN APPENDIX WITH OOP FOR THOSE WITH ESHI, H= POOR, FAIR, GOOD
table (H1 type2) (age_group1)    ,  statistic(mean SLF1  SLF2) nototals nformat(%5.2f) 
//collect title "Average OOP/year for those with PHI, MEPS, thousands of dollars"
//collect export "${out_tables}/OOP_PHI.tex", tableonly replace


replace SLF1=SLF1/5.2 // model units
replace SLF2=SLF2/5.2

* exporting the non-catastrophic and catastrophic expenditures for insured 
preserve
drop SLF2
sort type2 H1  age_group1
drop type2 H1  age_group1
outsheet using "${data_model}\ME_ins_1.txt", nolabel nonames replace	
restore

drop SLF1
sort type2 H1  age_group1
drop type2 H1  age_group1
outsheet using "${data_model}\ME_ins_2.txt", nolabel nonames replace		
}


* FOR NON-INSURED, we set their total ME = charges for those insured. SCALED BY .6
* for those without health shocks, set to zero.
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
keep if AGE1<65
keep if INSCOP_Y1==1 // this covers R1-R3 which we are using here.

**** CHARGES - SUMMARY STATS FOR THOSE INSURED VS UNINSURED. 
gen I_Charges = 1 if Charges_plus_RX<.5 & Charges_plus_RX!=.
replace I_Charges = 0 if Charges_plus_RX>=.5 & Charges_plus_RX!=.

gen I_EXP = 1 if Expenditures_incl_RX<.5 & Expenditures_incl_RX!=.
replace I_EXP = 0 if Expenditures_incl_RX>=.5 & Expenditures_incl_RX!=.

gen I_suspicious1 = 1 if Charges_plus_RX<Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.
replace I_suspicious1 = 0 if Charges_plus_RX>=Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.

gen MCAID_yr1= 0 if MCAID1==0 & MCAID2==0 & MCAID3==0 
replace MCAID_yr1= 1 if MCAID1==1 | MCAID2==1 | MCAID3==1

gen age_group1=25 if AGE1>=25 & AGE1<30
replace age_group1=30 if AGE1>=30 & AGE1<35
replace age_group1=35 if AGE1>=35 & AGE1<40
replace age_group1=40 if AGE1>=40 & AGE1<45
replace age_group1=45 if AGE1>=45 & AGE1<50
replace age_group1=50 if AGE1>=50 & AGE1<55
replace age_group1=55 if AGE1>=55 & AGE1<60
replace age_group1=60 if AGE1>=60 & AGE1<65

* KEEP UNINSURED
keep if INSCOV_Y1==1  & MCAID_yr1== 0 & I_suspicious1 == 0 & H1!=. & Charges_plus_RX!=. & type2!=.  


* checking if discontinuity based on years
gen I_time_period=0 if year<=2006
replace I_time_period=1 if year>2006
reg Charges_plus_RX i.age_group1 i.H1 i.type2 i.I_time_period 
reg Charges_plus_RX i.age_group1 i.H1  i.I_time_period 

keep if year>2006 // comment this out to get tables in appendix. 

replace Charges_plus_RX=Charges_plus_RX *.6 // scaling by .6

bysort age_group1 H1 type2: egen p95=pctile(Charges_plus_RX) , p(95)
gen CHARGE1=Charges_plus_RX if Charges_plus_RX<p95 
gen CHARGE2=Charges_plus_RX if Charges_plus_RX>=p95  

fillin age_group1 H1 type2
reg CHARGE1 i.age_group1 i.H1 i.type2 
predict ME1 if CHARGE1!=. | _fillin==1
reg CHARGE2 i.age_group1 i.H1 i.type2 
predict ME2 if CHARGE2!=.  | _fillin==1

collapse (mean) ME1 ME2, by(age_group1 H1 type2)
replace ME2=0 if ME2<0
gen ratio = ME1/ME2
tabstat ratio
replace ME1=.1255698*ME2 if ME1==. | ME1<0
drop ratio
drop if age_group1==.

* STATS FOR TABLES 29, 30 AND 31
* for those without health shocks, set to zero.
* to replicate the tables, comment out the line keeping only years after 2006. But in the end we used only years after 2006 for the numbers that we feed into the model. It makes only a small difference
preserve
rename ME1 ME1_
rename ME2 ME2_
reshape wide ME1_ ME2_, i(H1 type2) j(age_group1)
order H1 type2 ME1_* ME2_*
restore


replace ME1=ME1/5.2 // model units
replace ME2=ME2/5.2

// replace to zero if no shocks
replace ME1=0 if type2==1
replace ME2=0 if type2==1

* EXPORT TO FILE FOR IMPORTING INTO MODEL
preserve
drop ME2
sort type2 H1  age_group1
drop type2 H1  age_group1
outsheet using "${data_model}\ME_no_ins_1.txt", nolabel nonames replace	
restore

drop ME1
sort type2 H1  age_group1
drop type2 H1  age_group1
outsheet using "${data_model}\ME_no_ins_2.txt", nolabel nonames replace	
}


* FOR RETIREES 
* OOP for those not on medicaid.
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
keep if AGE1>=65
drop if AGE1>=80
keep if INSCOP_Y1==1 // this covers R1-R3 which we are using here.
 
gen I_Charges = 1 if Charges_plus_RX<.5 & Charges_plus_RX!=.
replace I_Charges = 0 if Charges_plus_RX>=.5 & Charges_plus_RX!=.

gen I_EXP = 1 if Expenditures_incl_RX<.5 & Expenditures_incl_RX!=.
replace I_EXP = 0 if Expenditures_incl_RX>=.5 & Expenditures_incl_RX!=.

gen I_suspicious1 = 1 if Charges_plus_RX<Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.
replace I_suspicious1 = 0 if Charges_plus_RX>=Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.


gen MCAID_yr1= 0 if MCAID1==0 & MCAID2==0 & MCAID3==0 
replace MCAID_yr1= 1 if MCAID1==1 | MCAID2==1 | MCAID3==1

gen age_group1=65 if AGE1>=65 & AGE1<70
replace age_group1=70 if AGE1>=70 & AGE1<75
replace age_group1=75 if AGE1>=75 & AGE1<80

keep if  MCAID_yr1== 0 & I_suspicious1 == 0 & H1!=. & TOTSLF_Y1!=. & type2!=.  

bysort age_group1 H1 type2: egen p95=pctile(TOTSLF_Y1) , p(95)
gen OOP1=TOTSLF_Y1 if TOTSLF_Y1<p95 
gen OOP2=TOTSLF_Y1 if TOTSLF_Y1>=p95  

fillin age_group1 H1 type2
reg OOP1 i.age_group1 i.H1 i.type2 [pw=Sample_Weight]
predict SLF1 if OOP1!=. | _fillin==1
reg OOP2 i.age_group1 i.H1 i.type2 [pw=Sample_Weight]
predict SLF2 if OOP2!=.  | _fillin==1

collapse (mean) SLF1 SLF2, by(age_group1 H1 type2)

* THIS IS TABLE 32 IN THE APPENDIX
preserve
rename SLF1 SLF1_
rename SLF2 SLF2_
reshape wide SLF1_ SLF2_, i(H1 type2) j(age_group1)
order H1 type2 SLF1_* SLF2_*
restore

replace SLF1=SLF1/5.2 // model units
replace SLF2=SLF2/5.2

preserve
drop SLF2
sort type2 H1  age_group1
drop type2 H1  age_group1
outsheet using "${data_model}\ME_ret_1.txt", nolabel nonames replace	
restore

drop SLF1
sort type2 H1  age_group1
drop type2 H1  age_group1
outsheet using "${data_model}\ME_ret_2.txt", nolabel nonames replace	
}


*** We also want to impute the Charges for individuals in the model. Calculate these using those with ESHI AT AGES <65
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE1<65
keep if INSCOP_Y1==1 // this covers R1-R3 which we are using here.

**** CHARGES - SUMMARY STATS FOR THOSE INSURED VS UNINSURED. 
gen I_Charges = 1 if Charges_plus_RX<.5 & Charges_plus_RX!=.
replace I_Charges = 0 if Charges_plus_RX>=.5 & Charges_plus_RX!=.

gen I_EXP = 1 if Expenditures_incl_RX<.5 & Expenditures_incl_RX!=.
replace I_EXP = 0 if Expenditures_incl_RX>=.5 & Expenditures_incl_RX!=.

gen I_suspicious1 = 1 if Charges_plus_RX<Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.
replace I_suspicious1 = 0 if Charges_plus_RX>=Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.

gen MCAID_yr1= 0 if MCAID1==0 & MCAID2==0 & MCAID3==0 
replace MCAID_yr1= 1 if MCAID1==1 | MCAID2==1 | MCAID3==1

gen age_group1=25 if AGE1>=25 & AGE1<30
replace age_group1=30 if AGE1>=30 & AGE1<35
replace age_group1=35 if AGE1>=35 & AGE1<40
replace age_group1=40 if AGE1>=40 & AGE1<45
replace age_group1=45 if AGE1>=45 & AGE1<50
replace age_group1=50 if AGE1>=50 & AGE1<55
replace age_group1=55 if AGE1>=55 & AGE1<60
replace age_group1=60 if AGE1>=60 & AGE1<65

keep if INSCOV_Y1==1  & MCAID_yr1== 0 & I_suspicious1 == 0 & H1!=. & Charges_plus_RX!=. & type2!=.  


bysort age_group1 H1 type2: egen p95=pctile(Charges_plus_RX) , p(95)
gen CHARGE1=Charges_plus_RX if Charges_plus_RX<p95 
gen CHARGE2=Charges_plus_RX if Charges_plus_RX>=p95  

fillin age_group1 H1 type2
reg CHARGE1 i.age_group1 i.H1 i.type2 // [pw=Sample_Weight]
predict ME1 if CHARGE1!=. | _fillin==1
reg CHARGE2 i.age_group1 i.H1 i.type2 // [pw=Sample_Weight]
predict ME2 if CHARGE2!=.  | _fillin==1

collapse (mean) ME1 ME2, by(age_group1 H1 type2)
replace ME2=0 if ME2<0
gen ratio = ME1/ME2
tabstat ratio
replace ME1=.1145553*ME2 if ME1==. | ME1<0
drop ratio
drop if age_group1==.

replace ME1=ME1/5.2 // model units
replace ME2=ME2/5.2

preserve
drop ME2
sort type2 H1  age_group1
drop type2 H1  age_group1
outsheet using "${data_model}\Charges_1.txt", nolabel nonames replace	
restore

drop ME1
sort type2 H1  age_group1
drop type2 H1  age_group1
outsheet using "${data_model}\Charges_2.txt", nolabel nonames replace	

}

 

*** OOP distribution - these are the MEPS data numbers that will be used later to create Figure 12 in the appendix. - shows concentration of OOP in small fraction of individuals. 
qui{
clear
use "${data}/MEPS_merged_Health_Whites.dta"
set more off
keep if AGE1<65 & AGE1>24
keep if INSCOP_Y1==1 // this covers R1-R3 which we are using here.

gen I_Charges = 1 if Charges_plus_RX<.5 & Charges_plus_RX!=.
replace I_Charges = 0 if Charges_plus_RX>=.5 & Charges_plus_RX!=.

gen I_Charges_alt = 1 if Charges_plus_RX<.2 & Charges_plus_RX!=.
replace I_Charges_alt = 0 if Charges_plus_RX>=.2 & Charges_plus_RX!=.

gen I_EXP = 1 if Expenditures_incl_RX<.5 & Expenditures_incl_RX!=.
replace I_EXP = 0 if Expenditures_incl_RX>=.5 & Expenditures_incl_RX!=.

gen I_suspicious1 = 1 if Charges_plus_RX<Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.
replace I_suspicious1 = 0 if Charges_plus_RX>=Expenditures_incl_RX & Expenditures_incl_RX!=. & Charges_plus_RX!=.
tab  I_suspicious1
drop if I_suspicious1 == 1 // this barely makes a difference

gen PHI=0 if INSCOV_Y1==2 | INSCOV_Y1==3
replace PHI=1 if INSCOV_Y1==1

rename TOTSLF_Y1 OOP
replace OOP=OOP*1000

gen MCAID_yr1= 0 if MCAID1==0 & MCAID2==0 & MCAID3==0 
replace MCAID_yr1= 1 if MCAID1==1 | MCAID2==1 | MCAID3==1

drop if PHI==1 & EMP_Y1==0 // this barely makes a difference
table PHI  [aw=Sample_Weight], stat(p10 OOP) stat(p25 OOP) stat(p50 OOP) stat(p75 OOP) stat(p90 OOP) stat(p95 OOP) 

table INSCOV_Y1  [aw=Sample_Weight], stat(p10 OOP) stat(p25 OOP) stat(p50 OOP) stat(p75 OOP) stat(p90 OOP) stat(p95 OOP) 

table INSCOV_Y1 if INSCOV_Y1!=2 & MCAID_yr1!= 1  [aw=Sample_Weight], stat(p10 OOP) stat(p25 OOP) stat(p50 OOP) stat(p75 OOP) stat(p90 OOP) stat(p95 OOP) nformat(%5.0f) //if INSCOV_Y1!=3
collect title "OOP Distribution, MEPS"
collect export "${out_tables}/OOP_dist.tex", tableonly replace

table INSCOV_Y1 if  MCAID_yr1!= 1  [aw=Sample_Weight], stat(p10 OOP) stat(p25 OOP) stat(p50 OOP) stat(p75 OOP) stat(p90 OOP) stat(p95 OOP) nformat(%5.0f) //if INSCOV_Y1!=3

drop  if MCAID_yr1== 1 

//tabstat OOP, stat(p50 p80 p85 p90 p95 p99)
egen OOP_1 = pctile(OOP), p(99)
gen OOP_1_use = OOP if OOP>=OOP_1
egen OOP_1_total = total(OOP_1_use) 

egen OOP_5 = pctile(OOP), p(95)
gen OOP_5_use = OOP if OOP>=OOP_5
egen OOP_5_total = total(OOP_5_use) 

egen OOP_10 = pctile(OOP), p(90)
gen OOP_10_use = OOP if OOP>=OOP_10
egen OOP_10_total = total(OOP_10_use) 

egen OOP_15 = pctile(OOP), p(85)
gen OOP_15_use = OOP if OOP>=OOP_15
egen OOP_15_total = total(OOP_15_use) 

egen OOP_20 = pctile(OOP), p(80)
gen OOP_20_use = OOP if OOP>=OOP_20
egen OOP_20_total = total(OOP_20_use) 

egen OOP_50 = pctile(OOP), p(50)
gen OOP_50_use = OOP if OOP>=OOP_50
egen OOP_50_total = total(OOP_50_use) 

egen total_OOP=total(OOP)
replace OOP_1_total=OOP_1_total/total_OOP * 100
replace OOP_5_total=OOP_5_total/total_OOP* 100
replace OOP_10_total=OOP_10_total/total_OOP* 100
replace OOP_15_total=OOP_15_total/total_OOP* 100
replace OOP_20_total=OOP_20_total/total_OOP* 100
replace OOP_50_total=OOP_50_total/total_OOP* 100
table, stat(mean OOP_1_total OOP_5_total OOP_10_total OOP_15_total OOP_20_total OOP_50_total)

keep OOP_1_total OOP_5_total OOP_10_total OOP_15_total OOP_20_total OOP_50_total
rename OOP_1_total OOP_1
rename OOP_5_total OOP_5
rename OOP_10_total OOP_10
rename OOP_15_total OOP_15
rename OOP_20_total OOP_20
rename OOP_50_total OOP_50

collapse (mean) OOP_1 OOP_5 OOP_10 OOP_15 OOP_20 OOP_50
gen Data=.
reshape long OOP_, i(Data) j(percent)
drop Data
rename OOP_ OOP_MEPS
sort percent
save "${data}\OOP.dta", replace
}

